﻿using System;
using System.Linq;
using System.Data.SqlClient;
using System.Security.Cryptography.X509Certificates;
using AgnisDAL.AGNIS_FORMSDataSetTableAdapters;
using AgnisDAL.ServiceAgnis;


namespace AgnisDAL
{
    /// <summary>
    /// This Class will send an agnis form object (from AgnisTranslator project) to the staging database
    /// </summary>
    public class cAgnisDAL
    {
        #region local members
        private static String agnisFormsConnectionString;
        private static SqlConnection mySqlConnection;
        private static readonly AGNIS_FORMSDataSet dsAgnisDb = new AGNIS_FORMSDataSet();
        static readonly QueriesTableAdapter qAdapter = new QueriesTableAdapter();
        //Create and instanciate the table adapter 'frevisionTa'
        static readonly FORM_REVISIONTableAdapter frevisionTa = new FORM_REVISIONTableAdapter();
        static readonly MODULETableAdapter ModuleTA = new MODULETableAdapter();
        static readonly INSTITUTIONTableAdapter institutionTa = new INSTITUTIONTableAdapter();//rg 2012-05-08
        static readonly PROCESSING_STATUSTableAdapter ProcessingStatusTa = new PROCESSING_STATUSTableAdapter();
        static readonly INFORMATION_PROCESSTableAdapter InfoProcessTa = new INFORMATION_PROCESSTableAdapter();
        static readonly GENERATED_FORMS_LIST_WITH_STATUSTableAdapter GeneratedFormsListTa = new GENERATED_FORMS_LIST_WITH_STATUSTableAdapter();
        static readonly FORMTableAdapter FormTa = new FORMTableAdapter();
        static readonly QUESTIONTableAdapter QuestionTA = new QUESTIONTableAdapter();
        //Those Boolean variables indicate if the 'calculated' value already exist.
        static bool bFormExist;

        static bool bModuleExist;

        static bool bFormElementErrorExist;

        static bool bProcessingStatusExist;

        static bool bInformationProcess;

        static bool bCridExist;

        private static bool bQuestionExist;

        #endregion        

        public static void SetConnectionString(String cString)
        {
            agnisFormsConnectionString = cString;
            mySqlConnection = new SqlConnection(agnisFormsConnectionString);
            qAdapter.SetConnection(mySqlConnection);
            frevisionTa.Connection = mySqlConnection;
            ProcessingStatusTa.Connection = mySqlConnection;
            InfoProcessTa.Connection = mySqlConnection;
        }
        public static bool LoadAgnisStagingDb(AGNIS_FORMSDataSet dsAgnisStagingDb)
        {

            GeneratedFormsListTa.Connection = mySqlConnection;
            frevisionTa.Connection = mySqlConnection;
            FormTa.Connection = mySqlConnection;
            //Load the dataset
            GeneratedFormsListTa.Fill(dsAgnisStagingDb.GENERATED_FORMS_LIST_WITH_STATUS);
            frevisionTa.Fill(dsAgnisStagingDb.FORM_REVISION);
            FormTa.Fill(dsAgnisStagingDb.FORM);
            return true;
        }
        #region 'IF EXIST' Functions

        private static void FormExist(int formID)
        {
            bFormExist = qAdapter.FormIDExist(formID) != null;
        }

        private static void CridExistinHistoricTable(decimal decCrid,DateTime Date_of_Hstct, int FormNumber, int Visit_Id)
        {
            bCridExist = qAdapter.CridExist( decCrid,FormNumber, Date_of_Hstct, Visit_Id) != null;
        }

        /// <summary>
        /// Check even any Module ID refering to the FormRevisionID already exists
        /// </summary>
        /// <param name="ModuleID"></param>
        /// <param name="FormRevisionID"></param>
        private static void ModuleRefersToFormRevisionExist(int ModuleID, int FormRevisionID)
        {
            bModuleExist = qAdapter.ModuleIDExist(ModuleID, FormRevisionID) != null;
        }

        /// <summary>
        /// Check even question ID already exist into a module
        /// </summary>
        /// <param name="QuestionID"></param>
        /// <param name="ModuleID"> </param>
        private static void QuestionExist(int QuestionID, int ModuleID)
        {
            bModuleExist = qAdapter.QuestionIDExist(QuestionID, ModuleID) != null;
        }

        /// <summary>
        /// Check even FORM ELEMENT ERROR already exist in the database
        /// </summary>
        /// <param name="FormElementErrorId"></param>
        private static void FormElementErrorExist(int FormElementErrorId)
        {
            bFormElementErrorExist = qAdapter.FormElementErrorIDExist(FormElementErrorId) != 0;
        }

        /// <summary>
        /// Check even processing status ID already exist
        /// </summary>
        /// <param name="ProcessungStatusId"></param>
        /// <param name="FormRevisionID"></param>
        private static void ProcessingStatusExist(int ProcessungStatusId, int FormRevisionID)
        {
            bProcessingStatusExist = qAdapter.ProcessingStatusIDExist(ProcessungStatusId, FormRevisionID) != 0;
        }

        /// <summary>
        /// Check even information process ID already exist
        /// </summary>
        /// <param name="InformationProcessId"></param>
        private static void InformationProcessExist(int InformationProcessId)
        {
            bInformationProcess = qAdapter.InformationProcessIDExist(InformationProcessId) != 0;
        }

        #endregion

        #region LOADING Data  Functions

        
        
        /// <summary>
        ///  load Form_Revision object into 'Agnis classic Tables' then load into Historic table
        /// </summary>
        /// <param name="frevision"></param>
        /// <param name="myFormNumber"></param>
        /// <param name="myFormPublicID"></param>
        /// <param name="visit_id"></param>
        /// <param name="id"></param>
        /// <param name="idaa"></param>
        /// <param name="dateOfTransplant"></param>
        /// <param name="iCRID"></param>
        /// <param name="InstanceId"></param>
        /// <param name="iModulePublicId"></param>
        /// <param name="Version"></param>
        /// <param name="form_Id"></param>
        public static void SendFormRevisionToDB(FormRevision frevision, int myFormNumber, long myFormPublicID, int visit_id, int id, int idaa, DateTime dateOfTransplant, int iCRID, string InstanceId, int iModulePublicId, decimal Version, int form_Id)
        {
            if (isFormRevisionDataLoaded(frevision, myFormNumber, myFormPublicID, visit_id, id, idaa, dateOfTransplant, InstanceId, iModulePublicId, Version, form_Id))
            {                                
                // ---------------RRA's new comment-----20/01/2012
                //Before saving a row, we have to check if CRID has already been saved. 
                //If Yes,delete the former one
                //before saving new row in memory. 
                CridExistinHistoricTable(iCRID, dateOfTransplant, myFormNumber, visit_id);
                if (bCridExist)
                {
                    qAdapter.DeleteCrid(iCRID, myFormNumber, dateOfTransplant, visit_id);                    
                }
                SaveFormIntoGeneratedFormsList(frevision, myFormNumber, visit_id, id, idaa, dateOfTransplant, iCRID);
            }
        }

        /// <summary>
        /// Cette fonction est appelée pour une 1ère traduction/insertion du Formulaire.On ne considère pas les paramètres indicatifs de mise à jour.
        /// </summary>
        private static bool GlobalInitialLoading(FormRevision frevision, int myFormNumber)
        { 
            //on crèe un objet plus typé "objet-ligne" à partir d'une nouvelle ligne de la table form_revision (du ds)
            AGNIS_FORMSDataSet.FORM_REVISIONRow fRevisionRow = dsAgnisDb.FORM_REVISION.NewFORM_REVISIONRow();
            //Retrieve the value of each data
            frevision.formId = GenerateFormID(); // ?
            frevision.form.id = frevision.formId;
            fRevisionRow.FORM_REVISION_ID = frevision.formId;
            fRevisionRow.FORM_ID = (int)frevision.formId;
            fRevisionRow.FORM_REVISION_SEQ_NUM = 1;
            frevision.form.currentFormRevisionSequenceNumber = Convert.ToString(fRevisionRow.FORM_REVISION_SEQ_NUM);
            //fRevisionRow.MODIFICATION_TIME = Convert.ToDateTime("18/12/2010"); //test value
            //Add this row to FormRevision
            dsAgnisDb.FORM_REVISION.AddFORM_REVISIONRow(fRevisionRow);
            //Update the 'frevisionTa'
            frevisionTa.Update(dsAgnisDb.FORM_REVISION);
            //Fill FORM Table
            SendFormToDB(frevision.form);
            //Fill MODULE table
            SendModule(frevision, (int)fRevisionRow.FORM_REVISION_ID);
            //Fill FORM ELEMENT ERROR table
            SendFormElementError(frevision);

            //fill INFORMATION PROCESS table
            int iRank = 0;
            switch (myFormNumber)
            {
                case 2804: iRank = 0; break;
                case 2400: iRank = 1; break;
                case 2450: iRank = 2; break;
                case 2900: iRank = 3; break;
            }
            int infoProcessId = SendInformationProcess(iRank);
            SendProcessingStatus(frevision, (int)fRevisionRow.FORM_REVISION_ID, myFormNumber, infoProcessId);
            return true;
        }

        /// <summary>
        /// Cette fonction eest appelée lorsqu'on procède à une mise à jour. Elle doit considérer les divers flags indiquant la mise à jour:
        /// - MAJ :Insertion de l'instance_Id dans FORM
        /// - Insertion de LockOverrideFlag dans FORM_REVISION
        /// - Insertion d'une ligne dans MODULE (le module pUBLIC ID , varie selon les FORM)
        /// - Insertion d'une ligne dans QUESTION (ayant la valeur 2527889, identique à tous les FORM)
        /// </summary>
        /// <param name="frevision"></param>
        /// <param name="myFormNumber"></param>
        /// <param name="InstanceId"></param>
        /// <returns></returns>
        private static bool GlobalLoadingForUpdate(FormRevision frevision, int myFormNumber, string InstanceId)
        {
            //on crèe un objet plus typé "objet-ligne" à partir d'une nouvelle ligne de la table form_revision (du ds)
            AGNIS_FORMSDataSet.FORM_REVISIONRow fRevisionRow = dsAgnisDb.FORM_REVISION.NewFORM_REVISIONRow();
            //Retrieve the value of each data
            frevision.formId = GenerateFormID();
            frevision.form.id = frevision.formId;
            fRevisionRow.FORM_REVISION_ID = frevision.id;
            fRevisionRow.FORM_ID = (int)frevision.formId;
            fRevisionRow.FORM_REVISION_SEQ_NUM = 1;
            frevision.form.currentFormRevisionSequenceNumber = Convert.ToString(fRevisionRow.FORM_REVISION_SEQ_NUM);
            ////fRevisionRow.MODIFICATION_TIME = Convert.ToDateTime("18/12/2010"); //test value
            if (InstanceId != "")
            {
                fRevisionRow.LOCK_OVERRIDE_FLAG = 1;
            }

            //Add this row to FormRevision
            dsAgnisDb.FORM_REVISION.AddFORM_REVISIONRow(fRevisionRow);
            //Update the 'frevisionTa'
            frevisionTa.Update(dsAgnisDb.FORM_REVISION);

            //UpdateFormRevision(OutputFormRevisionId);




            //Fill FORM Table
            SendFormToDB(frevision.form);
            //Up FORM
            //UpdateForm(OutputFormRevisionId, InstanceId);

            //Fill MODULE table
            SendModule(frevision, (int)fRevisionRow.FORM_REVISION_ID);
            //insert Update-Module row
            // Plus besoin d'inserer une nouvelle ligne puisque FORMSNET le fait déjà !!!!
            //InsertModuleRow(OutputFormRevisionId, iModulePublicId, Version, InstanceId);

            

            //Fill FORM ELEMENT ERROR table
            SendFormElementError(frevision);

            //fill INFORMATION PROCESS table
            int iRank = 0;
            switch (myFormNumber)
            {
                case 2804: iRank = 0; break;
                case 2400: iRank = 1; break;
                case 2450: iRank = 2; break;
                case 2900: iRank = 3; break;
            }
            int infoProcessId = SendInformationProcess(iRank);
            SendProcessingStatus(frevision, (int)fRevisionRow.FORM_REVISION_ID, myFormNumber, infoProcessId);
            return true;
        }


        public static void UpdateInstitutionTable()
        {//rg 2012-05-08 updates the institution table in AGNIS Form to match current centre

            string command = "UPDATE INSTITUTION SET [UNIQUE_NAME]='cibmtr_center_number:" + Centre.Default.Cibmtrco + "' where [INSTITUTION_ID]=1";
            institutionTa.Adapter.UpdateCommand = new SqlCommand(command, institutionTa.Connection);           
            institutionTa.Connection.Open();
            institutionTa.Adapter.UpdateCommand.ExecuteNonQuery();
            institutionTa.Connection.Close();
        }
        //private static void UpdateForm(int iFormId, string instanceId)
        //{
        //    string command = "UPDATE FORM SET INSTANCE_ID = " + Convert.ToInt32(instanceId) + "WHERE FORM_ID = " + iFormId;
        //    FormTa.Adapter.UpdateCommand = new SqlCommand(command, FormTa.Connection);
        //    FormTa.Adapter.Update(dsAgnisDb.FORM);
        //    FormTa.Connection.Open();
        //    FormTa.Adapter.UpdateCommand.ExecuteNonQuery();
        //    FormTa.Connection.Close();
        //}

        public static bool isFormRevisionDataLoaded(FormRevision frevision, int myFormNumber, long myFormPublicID, int visit_id, int id, int idaa, DateTime dateOfTransplant, string InstanceId, int iModulePublicId, decimal Version, int FormId)
        {
            try
            {
                return InstanceId == "" ? GlobalInitialLoading(frevision, myFormNumber) : GlobalLoadingForUpdate(frevision, myFormNumber, InstanceId);
            }
            catch (Exception ex)
            {
                throw new Exception("Error at SendFormRevisionToDB() function", ex);
            }
        }
        public static bool SaveFormIntoGeneratedFormsList(FormRevision frevision, int myFormNumber, int visitId, int id, int idaa, DateTime dateOfTransplant, int iCrid)
        {
            try
            {
                AGNIS_FORMSDataSet.GENERATED_FORMS_LIST_WITH_STATUSRow GFormListRow = dsAgnisDb.GENERATED_FORMS_LIST_WITH_STATUS.NewGENERATED_FORMS_LIST_WITH_STATUSRow();
                GFormListRow.FORM_ID = frevision.formId;
                GFormListRow.FORM_REVISION_ID = frevision.id;
                GFormListRow.FORM_NUMBER = myFormNumber;
                GFormListRow.VISIT_ID = visitId;
                //GFormListRow.CRID = 0;
                GFormListRow.ID = id;
                GFormListRow.IDAA = idaa;
                GFormListRow.DATE_OF_HSCT = dateOfTransplant;
                GFormListRow.STATUS = frevision.status != "" ? "NULL" : frevision.status;
                GFormListRow.CRID = iCrid; // TO DO  : up
                string modificationTimeIsNow = DateTime.Now.ToLongDateString(); //will be the EBMT_LAST_MOD_TIME but should be other
                GFormListRow.EBMT_LAST_MOD_TIME = Convert.ToDateTime(modificationTimeIsNow);

                dsAgnisDb.GENERATED_FORMS_LIST_WITH_STATUS.AddGENERATED_FORMS_LIST_WITH_STATUSRow(GFormListRow);

                string command = "INSERT INTO GENERATED_FORMS_LIST ([FORM_ID]" +
                           ",[FORM_REVISION_ID]" +
                           ",[FORM_NUMBER]" +
                           ",[VISIT_ID]" +
                           ",[CRID]" +
                           ",[ID]" +
                           ",[IDAA]" +
                           ",[DATE_OF_HSCT]" +
                           ",[EBMT_LAST_MOD_TIME]" +
                           ",[STATUS]) " +
                           "VALUES ( " + GFormListRow.FORM_ID +
                           ", " + GFormListRow.FORM_ID +
                           ", " + GFormListRow.FORM_NUMBER +
                           ", " + GFormListRow.VISIT_ID +
                           ", " + GFormListRow.CRID +
                           ", " + GFormListRow.ID +
                           ", " + GFormListRow.IDAA +
                           ", CONVERT(datetime,'" + GFormListRow.DATE_OF_HSCT + "',103)" +
                           ", CONVERT(datetime,'" + GFormListRow.EBMT_LAST_MOD_TIME + "',103)" +
                           ", " + GFormListRow.STATUS + ")";
                GeneratedFormsListTa.Adapter.InsertCommand = new SqlCommand(command, GeneratedFormsListTa.Connection);
                GeneratedFormsListTa.Adapter.Update(dsAgnisDb.GENERATED_FORMS_LIST_WITH_STATUS);

                return true;

            }
            catch (Exception ex)
            {
                throw new Exception("Error at SaveFormIntoGeneratedFormsList() function", ex);
            }

        }

        /// <summary>
        /// Sending a processus status data
        /// </summary>
        /// <param name="fRevision"></param>
        /// <param name="FormRevisionID"></param>
        /// <param name="FormNumber"> </param>
        /// <param name="InfoProcessID"> </param>
        public static void SendProcessingStatus(FormRevision fRevision, int FormRevisionID, int FormNumber, int InfoProcessID)
        {
            int iRank = 0;
            switch (FormNumber)
            {
                case 2400: iRank = 1; break;
                case 2450: iRank = 2; break;
            }

            ProcessingStatusExist(GenerateProcessingStatusID(), FormRevisionID);
            if (!bProcessingStatusExist)
            {
                //create a specific typed row
                AGNIS_FORMSDataSet.PROCESSING_STATUSRow ProcessStatusRow = dsAgnisDb.PROCESSING_STATUS.NewPROCESSING_STATUSRow();
                //Retrieve the value of each data col
                ProcessStatusRow.PROCESSING_STATUS_ID = GenerateProcessingStatusID();
                ProcessStatusRow.INFORMATION_PROCESS_ID = InfoProcessID;
                ProcessStatusRow.FORM_REVISION_ID = FormRevisionID;
                ProcessStatusRow.PROCESSING_RANK = iRank;
                //ProcessStatusRow.PARENT_PROCESSING_STATUS_ID = ? 
                ProcessStatusRow.VALUE = "READY";
                //
                dsAgnisDb.PROCESSING_STATUS.AddPROCESSING_STATUSRow(ProcessStatusRow);
                ProcessingStatusTa.Update(dsAgnisDb.PROCESSING_STATUS);

            }
        }
        public static int SendInformationProcess(int iRankInfo)
        {
            InformationProcessExist(GenerateInfoProcessID());
            if (!bInformationProcess)
            {
                //create a specific typed row
                AGNIS_FORMSDataSet.INFORMATION_PROCESSRow InfoProcessRow = dsAgnisDb.INFORMATION_PROCESS.NewINFORMATION_PROCESSRow();
                //Retrieve the value of each col
                InfoProcessRow.INFORMATION_PROCESS_ID = GenerateInfoProcessID();
                InfoProcessRow.UNIQUE_NAME = Convert.ToString(InfoProcessRow.INFORMATION_PROCESS_ID);
                InfoProcessRow.PROCESS_TYPE = "SUBMIT";
                InfoProcessRow.PROCESS_STATUS = "READY";
                InfoProcessRow.PROCESSING_RANK = iRankInfo;
                //
                dsAgnisDb.INFORMATION_PROCESS.AddINFORMATION_PROCESSRow(InfoProcessRow);
                InfoProcessTa.Update(dsAgnisDb.INFORMATION_PROCESS);
                return (int)InfoProcessRow.INFORMATION_PROCESS_ID;
            }
            return -1;
        }

        /// <summary>
        /// Sending a Form
        /// </summary>
        /// <param name="Frm"></param>
        public static void SendFormToDB(Form Frm)
        {
            int i = (int)Frm.id;
            FormExist(i);
            //If the Form row does not exist yet, we save it

            if (!bFormExist)
            {
                //Create a specific typed row from the ds FORM table rows
                AGNIS_FORMSDataSet.FORMRow fRow = dsAgnisDb.FORM.NewFORMRow();
                fRow.FORM_ID = i;
                fRow.PUBLISHER_INSTITUTION_ID = (int)Frm.publisherInstitutionId;
                if (Frm.instanceId != "")
                {
                    fRow.INSTANCE_ID = Frm.instanceId;
                }
                fRow.ORIGINATOR_INSTITUTION_ID = 1;//We need to change that in order to take the right INStit_ID
                fRow.PUBLIC_ID = (int)Frm.publicId;
                fRow.VERSION = Frm.version;
                fRow.CURRENT_FORM_REVISION_SEQ_NUM = Convert.ToInt32(Frm.currentFormRevisionSequenceNumber);
                if (Frm.modificationTime != Convert.ToDateTime("01/01/0001 00:00:00"))
                {
                    fRow.MODIFICATION_TIME = Frm.modificationTime;
                }
                

                //on ajoute la-dite ligne à la form_revision
                dsAgnisDb.FORM.AddFORMRow(fRow);

                //Attention la colonne Form_ID est Identity = true                   
                FormTa.Update(dsAgnisDb.FORM);
            }
            else
            {
                throw new DALException("there is already a form with the same Form ID");
            }

        }


        /// <summary>
        /// Sending ModuleCollection []
        /// </summary>
        /// <param name="fRevision"></param>
        /// <param name="Identifiant_FormRevision"></param>
        public static void SendModule(FormRevision fRevision, int Identifiant_FormRevision)
        {
            fRevision.moduleCollection = fRevision.moduleCollection.ToArray();
            //Boucle sur la taille du tableau (=nombre d'instance de modules)
            foreach (Module ResultModuleInstanceToInsert in fRevision.moduleCollection)
            {
                ModuleRefersToFormRevisionExist(GenerateModuleID(), (int)fRevision.id);
                //Y a-t-il au moins un module associé à la FORM_REVISION ?
                if (!bModuleExist)
                {// If NOT , insert
                    AGNIS_FORMSDataSet.MODULERow ModuleRow = dsAgnisDb.MODULE.NewMODULERow();
                    //Retrieve the value of each data table
                    ModuleRow.MODULE_ID = GenerateModuleID();
                    ModuleRow.FORM_REVISION_ID = Identifiant_FormRevision;
                    ModuleRow.PUBLIC_ID = ResultModuleInstanceToInsert.publicId;
                    ModuleRow.VERSION = ResultModuleInstanceToInsert.version;
                    ModuleRow.REPEAT_SEQUENCE_NUMBER = Convert.ToInt16(ResultModuleInstanceToInsert.repeatSequenceNumber);
                    if (fRevision.modificationTime != Convert.ToDateTime("01/01/0001 00:00:00"))
                    {
                        ModuleRow.MODIFICATION_TIME = fRevision.modificationTime;
                    }
                    //on ajoute la-dite ligne à la form_revision                    
                    dsAgnisDb.MODULE.AddMODULERow(ModuleRow);
                    //on crèe et instancie la Table Adapter
                    //AgnisDAL.AGNIS_FORMSDataSetTableAdapters.MODULETableAdapter ModuleTA = new AgnisDAL.AGNIS_FORMSDataSetTableAdapters.MODULETableAdapter();
                    //                
                    ModuleTA.Update(dsAgnisDb.MODULE);
                    SendQuestion(ResultModuleInstanceToInsert, (int)ModuleRow.MODULE_ID);
                }
            }


        }

        /// <summary>
        /// Sending Question belonging to a module
        /// </summary>
        /// <param name="ModuleSent"></param>
        /// <param name="ModuleID"></param>
        public static void SendQuestion(Module ModuleSent, int ModuleID)
        {            
            ModuleSent.questionCollection = ModuleSent.questionCollection.ToArray();
            //
            foreach (Question ResultQuestionToInsert in ModuleSent.questionCollection)
            {
                
                QuestionExist(GenerateQuestionID(), ModuleID);
                if (!bQuestionExist)
                {
                    AGNIS_FORMSDataSet.QUESTIONRow QuestionRow = dsAgnisDb.QUESTION.NewQUESTIONRow();
                    //Retrieve the value of each data
                    QuestionRow.QUESTION_ID = GenerateQuestionID();
                    QuestionRow.MODULE_ID = ModuleID;
                    QuestionRow.DATA_ELEMENT_PUBLIC_ID = ResultQuestionToInsert.dataElementPublicId;
                    QuestionRow.DATA_ELEMENT_VERSION = ResultQuestionToInsert.dataElementVersion;
                    //QuestionRow.ALTERNATE_METADATA_TYPE = ResultQuestionToInsert.alternateMetadataType;
                    //QuestionRow.ALTERNATE_METADATA_ID = ResultQuestionToInsert.alternateMetadataId;
                    //RRA : ',' is used everywhere except for decimal value
                    //This replace should be done during the translation process - TO DO
                    decimal decValue;
                    QuestionRow.VALUE = decimal.TryParse(ResultQuestionToInsert.value, out decValue) ? decValue.ToString().Replace(',','.') : ResultQuestionToInsert.value;
                    //OVERRIDE CODE ALGORITHM: if VALUE = 'NA' then fill the ERROR_EXCEPTION column.
                   
                    switch (QuestionRow.VALUE)
                    {
                        case "NA":
                            QuestionRow.VALUE = null;
                            QuestionRow.ERROR_EXCEPTION = "NA";
                            break;
                        default:
                            QuestionRow.ERROR_EXCEPTION = ResultQuestionToInsert.errorException;
                        break;
                    }
                    QuestionRow.ALTERNATE_VALUE = ResultQuestionToInsert.alternateValue;
                    //QuestionRow.ERROR_EXCEPTION = ResultQuestionToInsert.errorException;
                    QuestionRow.DELETE_FLAG = Convert.ToByte(ResultQuestionToInsert.delete);
                    if (ResultQuestionToInsert.valueMeaningPublicId == 0)
                    {
                        //QuestionRow.VALUE_MEANING_PUBLIC_ID = null;
                    }
                    else
                    {
                        QuestionRow.VALUE_MEANING_PUBLIC_ID = ResultQuestionToInsert.valueMeaningPublicId;
                    }
                    QuestionRow.VALUE_MEANING_VERSION = ResultQuestionToInsert.valueMeaningVersion;
                    if (ResultQuestionToInsert.modificationTime != Convert.ToDateTime("01/01/0001 00:00:00"))
                    {
                        QuestionRow.MODIFICATION_TIME = ResultQuestionToInsert.modificationTime;
                    }
                    //QuestionRow.MODIFICATION_USER_ID = ResultQuestionToInsert.modificationUserId;
                    dsAgnisDb.QUESTION.AddQUESTIONRow(QuestionRow);
                    //AgnisDAL.AGNIS_FORMSDataSetTableAdapters.QUESTIONTableAdapter QuestionTA = new AgnisDAL.AGNIS_FORMSDataSetTableAdapters.QUESTIONTableAdapter();
                    QuestionTA.Update(dsAgnisDb.QUESTION);
                }
            }
        }





        /// <summary>
        /// Sending FORM ELEMENT ERROR 
        /// </summary>
        public static void SendFormElementError(FormRevision fRevision)
        {

            if (fRevision.errorCollection != null)
            {
                fRevision.errorCollection = fRevision.errorCollection.ToArray();

                //boucle sur le tableau FormElementErrorCollection
                foreach (FormElementError resultFormElementErrorToInsert in fRevision.errorCollection)
                {
                    //Generate the FormElementErrorID
                    int _FormElementErrorID = GenerateFormElementErrorID();
                    FormElementErrorExist(_FormElementErrorID);
                    if (!bFormElementErrorExist)
                    {
                        //Create a specific row
                        AGNIS_FORMSDataSet.FORM_ELEMENT_ERRORRow FormElementErrorRow = dsAgnisDb.FORM_ELEMENT_ERROR.NewFORM_ELEMENT_ERRORRow();
                        //Retrieve the value of each data
                        FormElementErrorRow.FORM_ELEMENT_ERROR_ID = _FormElementErrorID;
                        FormElementErrorRow.FORM_REVISION_ID = resultFormElementErrorToInsert.formRevisionId;
                        FormElementErrorRow.QUESTION_ID = resultFormElementErrorToInsert.questionId;
                        FormElementErrorRow.ERROR_TYPE = resultFormElementErrorToInsert.type;
                        FormElementErrorRow.ERROR_MESSAGE = resultFormElementErrorToInsert.message;
                        FormElementErrorRow.ERROR_RAW_DATA = resultFormElementErrorToInsert.rawData;
                        //FormElementErrorRow.MODIFICATION_TIME = resultFormElementErrorToInsert.modificationTime;
                        //FormElementErrorRow.MODIFICATION_USER_ID = resultFormElementErrorToInsert.modificationUserId;
                        dsAgnisDb.FORM_ELEMENT_ERROR.AddFORM_ELEMENT_ERRORRow(FormElementErrorRow);
                        FORM_ELEMENT_ERRORTableAdapter FormElementErrorTA = new FORM_ELEMENT_ERRORTableAdapter();
                        FormElementErrorTA.Update(dsAgnisDb.FORM_ELEMENT_ERROR);
                    }
                }
            }
        }

        #endregion

        #region ID GENERATION functions

        /// <summary>
        /// Increment the value ID of Form
        /// </summary>
        /// <returns></returns>
        private static int GenerateFormID()
        {

            try
            {
                int countFrm = 0;
                if (qAdapter.CountLastForm() != null)
                {
                    countFrm = Convert.ToInt32(qAdapter.CountLastForm());
                }
                return countFrm + 1;
            }
            catch (Exception ex)
            {
                throw new Exception("Error at Generation ID", ex);
            }
        }

        /// <summary>
        /// Increment the ID value of the module ID
        /// </summary>
        /// <returns></returns>
        private static int GenerateModuleID()
        {
            try
            {
                int CountModuleFormRevision = 0;
                if (qAdapter.CountLastModuleFormRevision() != null)
                {
                    CountModuleFormRevision = Convert.ToInt32(qAdapter.CountLastModuleFormRevision());
                }
                return CountModuleFormRevision + 1;
            }
            catch (Exception ex)
            {
                throw new Exception("Error at Generation ID", ex);
            }
        }

        /// <summary>
        /// Increment the ID value of FormElementErrorID
        /// </summary>
        /// <returns></returns>
        private static int GenerateFormElementErrorID()
        {
            int countFrmElementErrID = (int)qAdapter.CountFormElementError();
            return countFrmElementErrID + 1;
        }
        /// <summary>
        /// Increment Question ID value
        /// </summary>
        /// <returns></returns>
        private static int GenerateQuestionID()
        {
            int countQuestionID = (int)qAdapter.CountQuestion();
            return countQuestionID + 1;
        }
        /// <summary>
        /// Increment ProcessusStatus ID value
        /// </summary>
        /// <returns></returns>
        private static int GenerateProcessingStatusID()
        {
            int countPrStatus = (int)qAdapter.CountProcessingStatus();
            return countPrStatus + 1;
        }

        private static int GenerateInfoProcessID()
        {
            int countInfoProcess = (int)qAdapter.CountInfoProcess();
            return countInfoProcess + 1;
        }
        #endregion

        public static FormRevision SendFormRevision(FormRevision myGeneratedFormRevision)
        {
            FormHandlerService agnis_services = new FormHandlerService();

            X509Certificate2 x509 = new X509Certificate2(Properties.Settings.Default.AgnisCertificatesLocation + @"\" + Properties.Settings.Default.AgnisCertificatesFilename, Properties.Settings.Default.AgnisCertificatesPwd);
            agnis_services.ClientCertificates.Add(x509);
            SubmitFormRevisionRequestFormRevision formRevisionReq = new SubmitFormRevisionRequestFormRevision();
            formRevisionReq.FormRevision = myGeneratedFormRevision;
            // "https://yew.nmdp.org:18443/wsrf/services/agnis/FormHandler";
            agnis_services.Url = Properties.Settings.Default.AgnisDAL_AgnisService_FormHandlerService;
            FormRevision reslt = agnis_services.submitFormRevision(formRevisionReq);
            return reslt;
        }
    }
}